/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.dal;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.myapp.data.UserData;
/**
 *
 * @author pandamint
 */
public class DBWorker {
    /*
     * To change this license header, choose License Headers in Project Properties.
     * To change this template file, choose Tools | Templates
     * and open the template in the editor.
     */

    public void menuList() {
        System.out.println("\nSelect an Option");
        System.out.println("1... List All");
        System.out.println("2... Insert");
        System.out.println("3... Update");
        System.out.println("4... Delete");
        System.out.println("5... Search");
        System.out.println("6... Exit");
        System.out.print("Enter your choice...");
    }

    /*private Connection CreateConnection() throws Exception
     {
     Connection dbcon;
     try 
     {
     dbcon = DriverManager.getConnection("jdbc:mysql://localhost:3306/codejava", "root", "root");
     }
     catch(Exception ex)
     {
     throw new Exception(ex);
               
     }
     return dbcon;
     }*/
    public void listAll() {
        Connection dbcon;

        try {
            dbcon = DriverManager.getConnection("jdbc:mysql://localhost:3306/codejava", "root", "root");
            Statement stmt = dbcon.createStatement();
            String query = "Select * from users";
            
            ResultSet rslt = stmt.executeQuery(query);
            while (rslt.next()) {
                System.out.println(rslt.getInt("Surname") + "\t" + rslt.getString("Name") + "\t" + rslt.getString("E-mail"));
            }
            dbcon.close();
        } catch (Exception ex) {
            System.out.print(ex);
        }
    }

    public void insert(UserData udata) throws Exception {
        Connection dbcon;
        try {
            dbcon = DriverManager.getConnection("jdbc:mysql://localhost:3306/codejava", "root", "root");
            PreparedStatement stmt = dbcon.prepareStatement("Insert into users(idusers) values(?)");

            System.out.println("Enter a new name to insert");
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            String data = br.readLine();
            stmt.setString(1, data);
            dbcon.close();
        } catch (Exception ex) {
            throw new Exception(ex); 
        }
    }

    public void update() {
        Connection dbcon;
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        try {
            dbcon = DriverManager.getConnection("jdbc:mysql://localhost:3306/codejava", "root", "root");
            PreparedStatement stmt = dbcon.prepareStatement("Update userlist set name=? where id = ?");
            System.out.println("Please enter the record number to modify");
            int row = Integer.parseInt(br.readLine());
            stmt.setInt(2, row);
            System.out.println("Enter the new data");
            String data = br.readLine();
            stmt.setString(1, data);
            if (stmt.executeUpdate() > 0) {
                System.out.println("Updated the record");
            } else {
                System.out.println("Record not found");
            }
            dbcon.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    public void delete() {
        Connection dbcon;
        try {
            dbcon = DriverManager.getConnection("jdbc:mysql://localhost:3306/codejava", "root", "root");
            Statement stmt = dbcon.createStatement();
            System.out.println("Enter the record to be deleted");
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            String row = br.readLine();
            if (stmt.executeUpdate("delete from users where idusers = " + row) > 0) {
                System.out.println("Row Deleted");
            } else {
                System.out.println("Row not found");
            }
            dbcon.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

    public void search() {
        Connection dbcon;
        try {
            dbcon = DriverManager.getConnection("jdbc:mysql://localhost:3306/codejava", "root", "root");
            Statement stmt = dbcon.createStatement();
            System.out.println("Enter the name to Search");
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            String key = br.readLine();
            ResultSet rslt = stmt.executeQuery("Select * from users where Name like '%" + key + "%'");
            while (rslt.next()) {
                System.out.println(rslt.getInt("idusers") + "\t" + rslt.getString("Name"));
            }
            dbcon.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }

     DBWorker() throws Exception
    {
            try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            throw new Exception(ex);
        }
    }
}
